from fastapi import APIRouter, HTTPException, Query
from typing import List
import pymysql
import requests
import time

postbar = APIRouter()


def get_db_connection():
    """
    创建并返回一个 MySQL 数据库连接
    """
    return pymysql.connect(
        host="localhost",
        user="root",  # 替换为你的 MySQL 用户名
        password="17884873770",  # 替换为你的 MySQL 密码
        database="spider01",  # 数据库名称
        charset="utf8mb4"
    )


@postbar.get("/visualization/data")
async def get_visualization_data():
    """
    提供可视化统计数据
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 查询帖子总数和成员总数
        total_query = """
        SELECT 
            SUM(thread_count) AS total_threads, 
            SUM(member_count) AS total_members 
        FROM tieba_forum
        """
        cursor.execute(total_query)
        total_result = cursor.fetchone()
        total_threads = total_result[0] or 0
        total_members = total_result[1] or 0

        # 查询按月份统计创建贴吧的数量
        monthly_query = """
        SELECT 
            DATE_FORMAT(created_at, '%%Y-%%m') AS month, 
            COUNT(*) AS forum_count 
        FROM tieba_forum 
        WHERE created_at IS NOT NULL 
        GROUP BY month 
        ORDER BY month
        """
        cursor.execute(monthly_query)
        monthly_results = cursor.fetchall()
        monthly_data = [
            {"month": row[0], "forum_count": row[1]} for row in monthly_results
        ]

        # 查询热门贴吧（按帖子数排序前5名）
        popular_query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum 
        ORDER BY thread_count DESC 
        LIMIT 5
        """
        cursor.execute(popular_query)
        popular_results = cursor.fetchall()
        popular_forums = [
            {"forum_name": row[0], "thread_count": row[1]} for row in popular_results
        ]

        # 返回统计数据
        return {
            "code": 200,
            "data": {
                "total_threads": total_threads,
                "total_members": total_members,
                "monthly_data": monthly_data,
                "popular_forums": popular_forums,
            },
        }

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        # 确保连接关闭
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()


@postbar.get("/forums", response_model=dict)
async def get_postbar_data(
        page: int = Query(1, ge=1, description="页码"),
        page_size: int = Query(10, ge=1, le=100, description="每页数量"),
):
    """
    获取贴吧详细数据（包括头像展示），支持分页
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 查询总条数
        count_query = "SELECT COUNT(*) FROM tieba_forum"
        cursor.execute(count_query)
        total = cursor.fetchone()[0]

        # 查询分页数据
        offset = (page - 1) * page_size
        query = """
        SELECT 
            forum_id, 
            forum_name, 
            avatar, 
            thread_count, 
            member_count, 
            created_at 
        FROM tieba_forum
        LIMIT %s OFFSET %s
        """
        cursor.execute(query, (page_size, offset))
        results = cursor.fetchall()

        # 格式化结果为 JSON 格式
        forums = [
            {
                "forum_id": row[0],
                "forum_name": row[1],
                "avatar": row[2],
                "thread_count": row[3],
                "member_count": row[4],
                "created_at": row[5].strftime("%Y-%m-%d %H:%M:%S") if row[5] else None
            }
            for row in results
        ]

        return {"code": 200, "total": total, "current": page, "data": forums}

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        # 确保连接关闭
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()


# 爬虫接口
@postbar.get("/spider")
async def run_spider():
    """
    运行爬虫，获取贴吧数据并保存到数据库
    """
    for page in range(1, 17):
        fetch_data(page)
    return {"code": 200, "message": "爬取成功"}


# 保存数据到 MySQL
def save_data_to_mysql(item):
    """
    保存数据到 MySQL，如果记录已存在则更新
    """
    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            sql = """
                INSERT INTO tieba_forum (forum_id, forum_name, avatar, thread_count, member_count)
                VALUES (%s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                thread_count = VALUES(thread_count),
                member_count = VALUES(member_count),
                avatar = VALUES(avatar)
            """
            cursor.execute(sql, (
                item["forum_id"],
                item["forum_name"],
                item.get("avatar", ""),
                item["thread_count"],
                item["member_count"]
            ))
        connection.commit()
    except Exception as e:
        print(f"保存数据时发生错误: {e}")
    finally:
        connection.close()


# 爬虫逻辑
def fetch_data(page: int):
    """
    获取贴吧数据并保存到数据库
    """
    headers = {
        "Accept": "*/*",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36",
    }
    cookies = {
        # 替换为实际的 Cookies 信息
        "BIDUPSID": "example_bid",
        "BDUSS": "example_bduss",
    }
    url = "https://tieba.baidu.com/f/index/rcmdForum"
    params = {"pn": str(page), "rn": "12"}

    try:
        response = requests.get(url, headers=headers, cookies=cookies, params=params)
        response.raise_for_status()
        data = response.json()

        for item in data["data"]["forum_info"]:
            save_data_to_mysql(item)
    except requests.exceptions.RequestException as e:
        print(f"请求失败: {e}")
    except KeyError as e:
        print(f"解析数据失败: {e}")


@postbar.get("/visualization/data")
async def get_visualization_data():
    """
    提供可视化统计数据
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 查询帖子总数和成员总数
        total_query = """
        SELECT 
            SUM(thread_count) AS total_threads, 
            SUM(member_count) AS total_members 
        FROM tieba_forum
        """
        cursor.execute(total_query)
        total_result = cursor.fetchone()
        total_threads = total_result[0] or 0
        total_members = total_result[1] or 0

        # 查询按月份统计创建贴吧的数量
        monthly_query = """
        SELECT 
            DATE_FORMAT(created_at, '%%Y-%%m') AS month, 
            COUNT(*) AS forum_count 
        FROM tieba_forum 
        WHERE created_at IS NOT NULL 
        GROUP BY month 
        ORDER BY month
        """
        cursor.execute(monthly_query)
        monthly_results = cursor.fetchall()
        monthly_data = [
            {"month": row[0], "forum_count": row[1]} for row in monthly_results
        ]

        # 查询热门贴吧（按帖子数排序前5名）
        popular_query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum 
        ORDER BY thread_count DESC 
        LIMIT 5
        """
        cursor.execute(popular_query)
        popular_results = cursor.fetchall()
        popular_forums = [
            {"forum_name": row[0], "thread_count": row[1]} for row in popular_results
        ]

        # 返回统计数据
        return {
            "code": 200,
            "data": {
                "total_threads": total_threads,
                "total_members": total_members,
                "monthly_data": monthly_data,
                "popular_forums": popular_forums,
            },
        }

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        # 确保连接关闭
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()


@postbar.get("/forums/top-threads", response_model=dict)
async def get_top_threads():
    """
    获取帖子数量前 10 名的贴吧
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 查询帖子数量前 10 名的贴吧
        query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum
        ORDER BY thread_count DESC
        LIMIT 10
        """
        cursor.execute(query)
        results = cursor.fetchall()

        # 格式化结果为 JSON 数据
        top_forums = [
            {"forum_name": row[0], "thread_count": row[1]} for row in results
        ]

        return {"code": 200, "data": top_forums}

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()

@postbar.get("/forums/visualization-data", response_model=dict)
async def get_combined_data():
    """
    返回用于柱状图、折线图和饼图的数据
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 柱状图数据：总帖数和成员总数
        total_query = """
        SELECT 
            SUM(thread_count) AS total_threads, 
            SUM(member_count) AS total_members 
        FROM tieba_forum
        """
        cursor.execute(total_query)
        total_data = cursor.fetchone()
        total_threads = total_data[0] or 0
        total_members = total_data[1] or 0

        # 折线图数据：帖子数量前 10 名的贴吧
        top_threads_query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum
        ORDER BY thread_count DESC
        LIMIT 10
        """
        cursor.execute(top_threads_query)
        top_threads = [
            {"forum_name": row[0], "thread_count": row[1]} for row in cursor.fetchall()
        ]

        # 示例饼图数据：按帖子数量占比
        pie_query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum
        ORDER BY thread_count DESC
        LIMIT 5
        """
        cursor.execute(pie_query)
        pie_data = [
            {"name": row[0], "value": row[1]} for row in cursor.fetchall()
        ]

        return {
            "code": 200,
            "data": {
                "bar_chart": {
                    "total_threads": total_threads,
                    "total_members": total_members,
                },
                "line_chart": top_threads,
                "pie_chart": pie_data,
            },
        }

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        if "cursor" in locals():
            cursor.close()
        if "connection" in locals():
            connection.close()

@postbar.get("/forums/analysis", response_model=dict)
async def analyze_forum_data():
    """
    返回数据分析结果，包括：
    1. 平均每个贴吧的帖子数量和成员数量
    2. 帖子数量最多的贴吧
    3. 成员数量最多的贴吧
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 平均每个贴吧的帖子数量和成员数量
        avg_query = """
        SELECT 
            AVG(thread_count) AS avg_threads, 
            AVG(member_count) AS avg_members 
        FROM tieba_forum
        """
        cursor.execute(avg_query)
        avg_data = cursor.fetchone()
        avg_threads = avg_data[0] or 0
        avg_members = avg_data[1] or 0

        # 帖子数量最多的贴吧
        max_threads_query = """
        SELECT 
            forum_name, 
            thread_count 
        FROM tieba_forum
        ORDER BY thread_count DESC
        LIMIT 1
        """
        cursor.execute(max_threads_query)
        max_threads_data = cursor.fetchone()
        max_threads_forum = {
            "forum_name": max_threads_data[0],
            "thread_count": max_threads_data[1],
        } if max_threads_data else {}

        # 成员数量最多的贴吧
        max_members_query = """
        SELECT 
            forum_name, 
            member_count 
        FROM tieba_forum
        ORDER BY member_count DESC
        LIMIT 1
        """
        cursor.execute(max_members_query)
        max_members_data = cursor.fetchone()
        max_members_forum = {
            "forum_name": max_members_data[0],
            "member_count": max_members_data[1],
        } if max_members_data else {}

        return {
            "code": 200,
            "data": {
                "average": {
                    "avg_threads": avg_threads,
                    "avg_members": avg_members,
                },
                "max_threads_forum": max_threads_forum,
                "max_members_forum": max_members_forum,
            },
        }

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        if "cursor" in locals():
            cursor.close()
        if "connection" in locals():
            connection.close()


@postbar.get("/forums/data-models", response_model=dict)
async def get_data_models():
    """
    获取数据库中存储的模型信息
    """
    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        # 查询数据模型信息
        query = """
        SELECT table_name, table_rows, avg_row_length, data_length
        FROM information_schema.tables
        WHERE table_schema = 'tieba'
        """
        cursor.execute(query)
        results = cursor.fetchall()

        # 格式化结果为 JSON 格式
        models = [
            {
                "table_name": row[0],
                "row_count": row[1],
                "avg_row_length": row[2],
                "data_length": row[3]
            }
            for row in results
        ]

        return {"code": 200, "data": models}

    except pymysql.MySQLError as e:
        raise HTTPException(status_code=500, detail=f"数据库查询失败: {str(e)}")
    finally:
        # 确保连接关闭
        if 'cursor' in locals():
            cursor.close()
        if 'connection' in locals():
            connection.close()





